﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace TPAMain数据维护工具V2
{
    public partial class frmImportGxjy : Form
    {
        SqlTools sqt;

        public frmImportGxjy()
        {
            InitializeComponent();
            sqt = new SqlTools(GeneralTools.GetConnectionString(".", "TPAMain"));
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "*.xls|*.xls";
            ofd.Multiselect = false;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                txtExcel.Text = ofd.FileName;
                FileInfo fi = new FileInfo(ofd.FileName);
                GetExcelTableNames(txtExcel.Text);
                if (cmbSheets.Items.Count > 0)
                {
                    cmbSheets.SelectedIndex = 0;
                }
            }
        }

        private void GetExcelTableNames(string strEFN)
        {
            cmbSheets.Items.Clear();
            DataTable _dt = new DataTable();
            try
            {
                if (System.IO.File.Exists(strEFN))
                {
                    OleDbConnection _ExcelConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + strEFN);
                    _ExcelConn.Open();
                    _dt = _ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, null);
                    _ExcelConn.Close();
                    DataView dv = _dt.DefaultView;
                    dv.RowFilter = "cardinality='0'";
                    _dt = dv.ToTable();
                }
            }
            catch
            {
            }
            if (_dt.Rows.Count > 0)
            {
                for (int i = 0; i < _dt.Rows.Count; i++)
                {
                    cmbSheets.Items.Add(_dt.Rows[i]["Table_Name"]);
                }
            }
        }

        private void cmbSheets_SelectedIndexChanged(object sender, EventArgs e)
        {
            ReadExcel();
        }

        private void ReadExcel()
        {
            if (cmbSheets.SelectedIndex >= 0)
            {
                if (dgv1.Columns["工单号"] != null)
                {
                    dgv1.Columns.Remove("工单号");
                }
                if (dgv1.Columns["导入结果"] != null)
                {
                    dgv1.Columns.Remove("导入结果");
                }
                dgv1.Columns.Add("工单号", "工单号");
                dgv1.Columns.Add("导入结果", "导入结果");
                dgv1.DataSource = GetExcelDataTable(txtExcel.Text, cmbSheets.Text);
                if (dgv1.Rows.Count > 0)
                {
                    foreach (DataGridViewRow dgvr in dgv1.Rows)
                    {
                        dgvr.Cells["序号"].Value = dgvr.Index + 1;
                    }
                }
                dgv1.AutoResizeColumns();
            }
        }

        private DataTable GetExcelDataTable(string strEfn, string strEtn)
        {
            try
            {
                OleDbConnection _ExcelConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + strEfn);
                OleDbDataAdapter _ExcelOda = new OleDbDataAdapter("Select * from [" + strEtn + "]", _ExcelConn);
                DataTable _tmpDt = new DataTable();
                _ExcelConn.Open();
                _ExcelOda.Fill(_tmpDt);
                _ExcelConn.Close();
                return _tmpDt;
            }
            catch
            {
                return null;
            }
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            if (dgv1.Rows.Count == 0)
            {
                GeneralTools.ShowInfoMsg("没有可导入的数据！");
                return;
            }
            if (dgv1.Columns["工序"] == null ||
                dgv1.Columns["批号"] == null ||
                dgv1.Columns["型号规格"] == null ||
                dgv1.Columns["数量"] == null ||
                dgv1.Columns["工单类型"] == null)
            {
                GeneralTools.ShowInfoMsg("数据格式有误，必须包含工序、批号、型号规格、数量、工单类型，5个字段才可导入！");
                return;
            }
            //初始化进度条
            Prgs1.Value = 0;
            Prgs1.Maximum = dgv1.Rows.Count;
            for (int i = 0; i < dgv1.Rows.Count; i++)
            {
                string strGD = string.Empty;
                string strMemo = string.Empty;
                if (dgv1.Columns["备注"] == null)
                {
                    strMemo = string.Empty;
                }
                else 
                {
                    strMemo = GeneralTools.GetString(dgv1.Rows[i].Cells["备注"].Value, string.Empty);
                }
                dgv1.Rows[i].Cells["导入结果"].Value = Import(GeneralTools.GetString(dgv1.Rows[i].Cells["工序"].Value, string.Empty),
                                                                                        GeneralTools.GetString(dgv1.Rows[i].Cells["工单类型"].Value, string.Empty),
                                                                                        GeneralTools.GetString(dgv1.Rows[i].Cells["批号"].Value, string.Empty),
                                                                                        GeneralTools.GetString(dgv1.Rows[i].Cells["型号规格"].Value, string.Empty),
                                                                                        GeneralTools.GetDouble(dgv1.Rows[i].Cells["数量"].Value, 0d),
                                                                                        strMemo,
                                                                                        out strGD);
                dgv1.Rows[i].Cells["工单号"].Value = strGD;
                Prgs1.Value = i + 1;
                Application.DoEvents();
            }
        }

        /// <summary>
        /// 向数据库中插入工序结余记录
        /// </summary>
        /// <param name="strGx">所在工序</param>
        /// <param name="strGDType">工单类型</param>
        /// <param name="strSN">批号</param>
        /// <param name="strXhgg">型号规格（产品编码）</param>
        /// <param name="dQuantity">数量</param>
        /// <param name="strMemo">备注</param>
        /// <param name="strGD">工单号（返回值）</param>
        /// <returns>执行结果</returns>
        private string Import(string strGx, string strGDType, string strSN, string strXhgg, double dQuantity, string strMemo, out string strGD)
        {
            strGDType = strGDType.ToUpper();
            if (strGDType != "A" &&
                strGDType != "B" &&
                strGDType != "C")
            {
                strGD = string.Empty;
                return "导入失败：工单类型不正确;";
            }
            if (strGx.Trim().Equals(string.Empty))
            {
                strGD = string.Empty;
                return "导入失败：工序不能为空;";
            }
            if (strSN.Trim().Equals(string.Empty))
            {
                strGD = string.Empty;
                return "导入失败：批号不能为空;";
            }
            if (strXhgg.Trim().Equals(string.Empty))
            {
                strGD = string.Empty;
                return "导入失败：型号规格不能为空;";
            }
            if (dQuantity <0)
            {
                strGD = string.Empty;
                return "导入失败：" + dQuantity.ToString() + "不是合法的数值类型;";
            }
            //执行存储过程
            SqlConnection conn = sqt.GetConnection();
            SqlCommand cmd = new SqlCommand("[dbo].[InsertGxjy]", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            //工序名称
            SqlParameter sp = cmd.Parameters.Add("@GX", SqlDbType.VarChar);
            sp.Value = strGx;
            //工单类型
            sp = cmd.Parameters.Add("@GDType", SqlDbType.VarChar);
            sp.Value = strGDType;
            //批号
            sp = cmd.Parameters.Add("@SN", SqlDbType.VarChar);
            sp.Value = strSN;
            //型号规格
            sp = cmd.Parameters.Add("@Xhgg", SqlDbType.VarChar);
            sp.Value = strXhgg;
            //数量
            sp = cmd.Parameters.Add("@Quantity", SqlDbType.Decimal);
            sp.Value = dQuantity;
            //备注
            sp = cmd.Parameters.Add("@Memo", SqlDbType.VarChar);
            sp.Value = strMemo;
            //新工单号
            sp = cmd.Parameters.Add("@NewGD", SqlDbType.VarChar);
            sp.Direction = ParameterDirection.Output;
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                strGD  = GeneralTools.GetString(cmd.Parameters["@NewGD"].Value, string.Empty);
            }
            catch (Exception ex)
            {
                strGD = string.Empty;
                return "导入失败：" + ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return string.Empty;
        }
    }
}
